# function delivers table of every stop loss/win combination
# the output of this function is used to draw the stop cubes
stopDiagram <- function(group, pattern, Pversion=0, method="fixedPct1",
                        direction="long", duration=10, table="orderLogicTest",
                        orderby="stoplimit", ret="values") {
  if('package:reshape' %in% search() || require('reshape',quietly=TRUE)) {}
  if('package:ggplot2' %in% search() || require('ggplot2',quietly=TRUE)) {}
  if('package:lattice' %in% search() || require('lattice',quietly=TRUE)) {}
  
  if (orderby == "stoplimit") {
    ob <- "`stoplosslimit`, `stopwinlimit`"
  } else if (orderby == "mean") {
    ob <- "`returnmean` DESC, `returnddev` ASC, `returnsd` ASC, `stoplosslimit` ASC, `stopwinlimit` ASC "
  } else if (orderby == "meansd") {
    ob <- "(`returnmean`/`returnsd`) DESC, `returnmean` DESC, `stoplosslimit` ASC, `stopwinlimit` ASC "
  } else if (orderby == "meanddev") {
    ob <- "(`returnmean`/`returnddev`) DESC, `returnmean` DESC, `stoplosslimit` ASC, `stopwinlimit` ASC "    
  } else if (orderby == "expmeanddev") {
    ob <- "((EXP(`returnmean`)-1)/`returnddev`) DESC, `returnmean` DESC, `stoplosslimit` ASC, `stopwinlimit` ASC "  
  }
  
  sqlString <- function() {
    sql <- sprintf("SELECT stoplosslimit, stopwinlimit, numberoftrades, 
      pctstopped, stoplosspct, stopwinpct, returnmean, returnsd,
      returnddev, pctwin
      FROM `%s`
      WHERE `group` = '%s'
      AND `pattern` = '%s'
      AND `Pversion` = %d
      AND `duration` = %d
      AND `method` = '%s'
      AND `direction` ='%s'
      ORDER BY %s ", 
        table, group, pattern, Pversion, duration, method, direction, ob)
    return (sql)
  }
  
  if (ret=="values") {
    con <- connectToVadb()
    values <- dbGetQuery(con, sqlString())
  
    dbDisconnect(con)
    return(values)
  } else if (ret=="sql") {
    return (sqlString())
  }
}

# used to eliminates zero limits for drawing the stop cubes
eliminate <- function(values) {
  values <- eliminateData(values, "stoplosslimit", 0, keep=FALSE)
  values <- eliminateData(values, "stopwinlimit", 0, keep=FALSE)
  return(values)
}

eliminateData <- function(values, column, value, keep=TRUE) {
  posretdf <- values
  # loop throught every line
  pj <- 1
  for (i in 1:dim(values)[1]) {
    if (!(is.na(match (values[i,column], value)[1]))) {
      mat=TRUE
    } else {
      mat=FALSE
    }
    if (mat & keep | !keep & !mat) {
      posretdf[pj,] <- values[i,]
      pj <- pj+1
    }
  }
  return (posretdf[1:(pj-1),])
}

# used to alter zero limits in order to plot unlimited data on 
# right side of the stop cubes
alterzero <- function(values, replacement=10) {
  values <- alterData(values, "stoplosslimit", 0, replacement)
  values <- alterData(values, "stopwinlimit", 0, replacement)
  return(values)  
}

alterData <- function(values, column, value, replacement) {
  posretdf <- values
  # loop throught every line
  for (i in 1:dim(values)[1]) {
    if (!(is.na(match (values[i,column], value)[1]))) {
      posretdf[i,column] = replacement
    }
  }
  return (posretdf)
}